package edu.uoc.tdp.pac4.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.uoc.tdp.pac4.beans.Aula;
import edu.uoc.tdp.pac4.beans.TipusAula;

public class ClassroomManager {

	Connection con = null;
	PreparedStatement pst = null;
	ResultSet rs = null;


	public ClassroomManager() {

	}

	private Connection getConnection() throws SQLException {
		Connection conn = null;
		conn = DiskManager.getInstance().getConnection();
		return conn;
	}

	
	public Aula findClassroomById(Integer id) {
		Aula aula = new Aula();
		aula.setIdAula(id);
		List<Aula> classrooms = findClassrooms(aula);
		if(classrooms.size()!= 1){
			throw new RuntimeException("classroom not found");
		}
		return classrooms.get(0);
	}

	public List<Aula> listClassroom() {
		List<Aula> classrooms = findClassrooms(null);
		return classrooms;
	}

	/**
	 * 
	 * @return
	 */
	public List<Aula> findClassrooms(Aula crFilter) {
		List<Aula> classrooms = new ArrayList<Aula>();
		if(crFilter == null){
			crFilter = new Aula();
		}
		
		try {
			String querystring = "" +
					" SELECT id, nom, capacitat, tipusaula, localitzacio, datainactivitat" +
					" FROM AULA" +
					" WHERE '0'='0' ";
			
			
			querystring += " AND ( '0'='" + ((crFilter.getIdAula() == 0) ? 0 : 1) + "' OR  id = ? ) ";
			querystring += " AND ( '0'='" + ((crFilter.getNom() == null) ? 0 : 1)+ "' OR  nom = ? ) ";
			querystring += " AND ( '0'='" + ((crFilter.getCapacitat() == 0) ? 0 : 1) + "' OR  capacitat = ? ) ";
			querystring += " AND ( '0'='" + ((crFilter.getTipusAula() == null) ? 0 : 1) + "' OR  tipusaula = ?::TipusAula ) ";
			querystring += " AND ( '0'='" + ((crFilter.getLocalitzacio() == null) ? 0 : 1) + "' OR  localitzacio = ? ) ";
			querystring += " AND ( '0'='" + ((crFilter.getActive() == null) ? 0 : 1) + "' OR  datainactivitat " + ( (crFilter.isActive())?  " is null ":" is not null " ) + " ) ";
			
			System.out.println(querystring);
			
			con = getConnection();
			pst = con.prepareStatement(querystring);
			
			pst.setInt(1, crFilter.getIdAula());
			pst.setString(2, crFilter.getNom());
			pst.setInt(3, crFilter.getCapacitat());
			pst.setString(4,crFilter.getTipusAula()==null?null:crFilter.getTipusAula().name());
			pst.setString(5, crFilter.getLocalitzacio());
			//pst.setDate(6, (crFilter.getDataInactivitat()==null)?null:new java.sql.Date(crFilter.getDataInactivitat().getTime()));

			
			rs = pst.executeQuery();
			while (rs.next()) {

				Aula classroom = new Aula();

				classroom.setIdAula(rs.getInt(1));
				classroom.setNom(rs.getString(2));
				classroom.setCapacitat(rs.getInt(3));
				classroom.setTipusAula(TipusAula.valueOf(rs.getString(4)));
				classroom.setLocalitzacio(rs.getString(5));
				classroom.setDataInactivitat(rs.getDate(6));

				classrooms.add(classroom);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return classrooms;

	}

	public void createClassroom(Aula classroom) {

		try {
			con = getConnection();
			String pstQuery = "INSERT INTO" +
					" Aula(nom, capacitat, tipusaula, localitzacio, datainactivitat )"
					+ " VALUES(?,?,?::TipusAula,?,?)";
			pst = con.prepareStatement(pstQuery);
			pst.setString(1, classroom.getNom());
			pst.setInt(2, classroom.getCapacitat());
			pst.setString(3, classroom.getTipusAula().name());
			pst.setString(4, classroom.getLocalitzacio());
			pst.setDate(5, (classroom.getDataInactivitat()==null)? null: new java.sql.Date(classroom.getDataInactivitat().getTime()));
			
			pst.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	public void updateClassroom(Aula classroom) {
		
		try {
			String querystring = "UPDATE AULA" +
					" SET nom=?, capacitat=?, tipusaula=?::TipusAula, localitzacio=?, datainactivitat=? "
					+ " WHERE id=?";

			con = getConnection();
			pst = con.prepareStatement(querystring);
			pst.setString(1, classroom.getNom());
			pst.setInt(2, classroom.getCapacitat());
			pst.setString(3, classroom.getTipusAula().name());
			pst.setString(4, classroom.getLocalitzacio());
			pst.setDate(5, (classroom.getDataInactivitat()==null)?null:new java.sql.Date(classroom.getDataInactivitat().getTime()));
			pst.setInt(6, classroom.getIdAula());

			pst.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}
	
	
	
	
	
	

}
